# imports
import panel as pn
pn.extension('plotly')
import plotly.express as px
import pandas as pd
import hvplot.pandas
import matplotlib.pyplot as plt
import os
from pathlib import Path
from dotenv import load_dotenv

# Import the necessary CSVs to Pandas DataFrames
file_path = Path("Data/sfo_neighborhoods_census_data.csv")
sfo_data = pd.read_csv(file_path, index_col="year")
sfo_data.head()

file_path = Path("Data/neighborhoods_coordinates.csv")
sfo_data = pd.read_csv(file_path)
sfo_data.head()

file_path2 = Path("housing_units_per_year.csv")
data_group_per_year = pd.read_csv(file_path2)
data_group_per_year.columns= ['year','housing_units']
data_group_per_year .head(10)

file_path3 = Path("Average_of_housing_Cost_per_year.csv")
Average_of_housing_Cost_per_year = pd.read_csv(file_path3)
Average_of_housing_Cost_per_year.columns= ['year','sale_price_sqr_foot', 'gross_rent']
Average_of_housing_Cost_per_year.head(10)

file_path4 = Path("costs.csv")
df_costs = pd.read_csv(file_path4)
#df_costs.columns= ['year','neighborhood','sale_price_sqr_foot','housing_units' ,'gross_rent']
df_costs.head(10)

file_path5 = Path("top_ten.csv")
top_ten = pd.read_csv(file_path5)

top_ten.head(10)

file_path6 = Path("most_expensive.csv")
most_expensive = pd.read_csv(file_path6)
most_expensive.head(10)

file_path7 = Path("combined_df.csv")
combined_df = pd.read_csv(file_path7)
combined_df.head(10)

# Define Panel Visualization Functions
def housing_units_per_year():
    """Housing Units Per Year."""
    fig_housing_units = plt.figure()
    plot_housing_units = data_group_per_year.plot.bar()
    plot_housing_units.set_xlabel('Year', fontsize=12)
    plot_housing_units.set_ylabel('Housing Units', fontsize=12)
    plot_housing_units.set_title("Housing Units in San Francisco from 2010 to 2016", fontsize=15, fontweight='bold')
    housing_units_std =data_group_per_year.std()
    housing_units_min =min(data_group_per_year)
    housing_units_max =max(data_group_per_year)
    plot_housing_units.set_ylim(370000, 390000)
    plt.show()
    plt.close(fig_housing_units)
    
    return plot_housing_units

housing_units_per_year()

# Define Panel Visualization Functions
def average_gross_rent():
    """Average Gross Rent in San Francisco Per Year."""
    fig_Average_of_housing_Cost_per_year = plt.figure()
    plot_Average_of_housing_Cost_per_year = Average_of_housing_Cost_per_year['gross_rent'].plot(color = 'red')
    plot_Average_of_housing_Cost_per_year.set_xlabel('Year', fontsize=12)
    plot_Average_of_housing_Cost_per_year.set_ylabel('Gross Rent', fontsize=12)
    plot_Average_of_housing_Cost_per_year.set_title("Average Gross Rent by Year, San Francisco", fontsize=15, fontweight='bold')
    plt.show()
    plt.close(fig_Average_of_housing_Cost_per_year)
        
    return plot_Average_of_housing_Cost_per_year

average_gross_rent()

# Define Panel Visualization Functions
def average_sales_price():
    """Average Sales Price Per Year."""
    fig_Average_of_housing_Cost_per_year = plt.figure()
    plot_Average_of_housing_Cost_per_year = Average_of_housing_Cost_per_year['sale_price_sqr_foot'].plot(color = 'purple')
    plot_Average_of_housing_Cost_per_year.set_xlabel('Year', fontsize=12)
    plot_Average_of_housing_Cost_per_year.set_ylabel('Price per SqFt', fontsize=12)
    plot_Average_of_housing_Cost_per_year.set_title("Average Sale Price per sqrfoot by Year, San Francisco", fontsize=15, fontweight='bold')
    plt.show()
    plt.close(fig_Average_of_housing_Cost_per_year)
    
    return plot_Average_of_housing_Cost_per_year

average_sales_price()

# Define Panel Visualization Functions
def average_price_by_neighborhood():
    """Average Prices by Neighborhood."""
    average_price_by_neighborhood = df_costs.hvplot.line(x='year',  
    y=['sale_price_sqr_foot'], 
    groupby=(['neighborhood']),
    rot=90).opts(yformatter="%.0f",  
    title="Average Price per sq ft.",
    ylabel = 'Average sale_price_sqr_foot', 
    xlabel='Year')
    
    return average_price_by_neighborhood

average_price_by_neighborhood()

# Define Panel Visualization Functions
def neighborhood_map(test=True):
    """Neighborhood Map."""
    map_box = px.scatter_mapbox(
    combined_df,
    lat="Lat",
    lon="Lon",
    size="sale_price_sqr_foot",
    color="gross_rent",
    zoom=4,
    title= "Average Values per Neighborhood in San Francisco")

    
    if test:
        map_box.update_layout(
            mapbox_style="carto-positron")
        map_box.show()

    return map_box

neighborhood_map(test=False)

House_avg_price = pn.Row(housing_units_per_year(), average_price_by_neighborhood())
House_avg_rent_sales = pn.Row( average_gross_rent(),  average_sales_price())
Expensive_Houses = pn.Row( top_most_expensive_neighborhoods(), most_expensive_neighborhoods_rent_sales())
Parallel_Cat = pn.Row(parallel_coordinates(), parallel_categories())

# Create a tab layout for the dashboard
San_Francisco_Housing_Cost_Analysis_pop_dasboard = pn.Tabs(
    ( "SF_Housing",SF_Housing),
    ("SF_Most_Expensive",SF_Most_Expensive))

San_Francisco_Housing_Cost_Analysis_pop_dasboard

# Serve the dashboard
San_Francisco_Housing_Cost_Analysis_pop_dasboard.servable()

 housing_units_per_year()

 average_gross_rent()

 average_sales_price()

 average_price_by_neighborhood()

 top_most_expensive_neighborhoods()

 most_expensive_neighborhoods_rent_sales()

neighborhood_map(test=False)

 neighborhood_map()

 parallel_categories()

 parallel_coordinates()

 sunburst()